package Servlet;

import java.sql.*;
import java.util.ArrayList;
public class Query {
    private static final String URL="jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

    private static final String USER="root";
    private static final String PASSWORD="123456";
    private static Connection conn=null;


    public ArrayList<Dao> query(String s){   //这里的query()方法是将从数据库中读取到的数据存储到集合中
        System.out.println(s);
        ResultSet rs = null;
        Statement st = null;
        ArrayList<Dao> studentList=new ArrayList<Dao>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); //加载数据库驱动
        }catch (Exception e){
            e.printStackTrace();
        }

        try{

            conn =DriverManager.getConnection(URL, USER, PASSWORD);  //获取数据库连接对象
            System.out.println("数据库连接成功");
            st = conn.createStatement();
            rs = st.executeQuery("SELECT semester,classNo, \n" +
                    "       SUM(CASE WHEN gender=1 THEN num ELSE 0 END) AS num_male,\n" +
                    "       SUM(CASE WHEN gender=0 THEN num ELSE 0 END) AS num_female \n" +
                    "       from student where semester="+s+" and classNo>'2020340794' group by semester,classNo;");
            while(rs.next()){
                Dao student=new Dao();
                student.setSemester(rs.getInt("semester"));
                student.setClassNo(rs.getString("classNo"));
                student.setMale(rs.getInt("num_male"));
                student.setFemale(rs.getInt("num_female"));
                studentList.add(student);     //将从数据库中读取到的数据 以bar对象的方式存储到 集合中
            }
            conn.close();  //断开连接
        }catch(SQLException e){
            e.printStackTrace();
        }


        return studentList;   //返回该集合
    }

}